
package com.ncloud.crm.mapper;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.ncloud.crm.entity.BsContract;
@Mapper
public interface BsContractMapper {

	@Insert("insert into bs_contract(cust_id, contract_id,contract_name, "
			+ "file_path, sign_date, exp_date, eff_date, sale_id, state, create_time, create_user, update_time, update_user) "
			+ "values(#{custId}, #{contractId}, #{contractName}, #{filePath}, #{signDate}, #{expDate}, #{effDate}, #{saleId}, #{state}, " +
			"#{createTime}, #{createUser}, #{updateTime}, #{updateUser})")
	int insertContract(BsContract contract);

	@Update("update bs_contract set contract_name = #{contractName}, file_path = #{filePath},"
			+ " sign_date = #{signDate}, exp_date = #{expDate}, eff_date = #{effDate}, sale_id = #{saleId},update_time = #{updateTime},"
			+ " update_user =  #{updateUser} where contract_id = #{contractId}")
	int updateContract(BsContract contract);
	
	//查询项目对应主合同信息
	 @Select(value = {"select a.* from bs_contract a left join bs_project b on a.project_id = b.project_id where a.child_contract_Inst_id = a.contract_inst_id "
	 		+ " and b.project_id = #{0} and b.manager = #{1} and a.state ='1'"})
	 @Results({ @Result(column = "project_id", property = "projectId"), @Result(column = "child_contract_inst_id", property = "childContractInstId"),
       @Result(column = "contract_inst_id", property = "contractInstId"),@Result(column = "contract_id", property = "contractId"),
       @Result(column = "contract_name", property = "contractName"),@Result(column = "file_path", property = "filePath"),
       @Result(column="sign_date",property="signDate"),@Result(column="exp_date",property="expDate"),
       @Result(column="eff_date",property="effDate"),@Result(column="sale_id",property="saleId"),
       @Result(column="state",property="state"),@Result(column="create_time",property="createTime"),
       @Result(column="create_user",property="createUser"),@Result(column="update_time",property="updateTime"),
       @Result(column="update_user",property="updateUser")})
	 public List<BsContract> getBsContracts(String projectId,String manager);
	//查询项目主合同下的子合同信息
	@Select(value = {"select a.* from bs_contract a left join bs_project b on a.project_id = b.project_id where a.child_contract_Inst_id != a.contract_inst_id "
			+ " and b.project_id = #{0} and b.manager = #{1} and a.contract_inst_id = #{2} and a.state='1'"})
	@Results({ @Result(column = "project_id", property = "projectId"), @Result(column = "child_contract_inst_id", property = "childContractInstId"),
			@Result(column = "contract_inst_id", property = "contractInstId"),@Result(column = "contract_id", property = "contractId"),
			@Result(column = "contract_name", property = "contractName"),@Result(column = "file_path", property = "filePath"),
			@Result(column="sign_date",property="signDate"),@Result(column="exp_date",property="expDate"),
			@Result(column="eff_date",property="effDate"),@Result(column="sale_id",property="saleId"),
			@Result(column="state",property="state"),@Result(column="create_time",property="createTime"),
			@Result(column="create_user",property="createUser"),@Result(column="update_time",property="updateTime"),
			@Result(column="update_user",property="updateUser"),@Result(column="remark",property="remark")})
	public List<BsContract> getBsContractsChild(String projectId,String manager,Long childContractInstId);


	/*查询销售人员对应主合同信息
	用途：我的合同
	*/
	@Select(value = {"select a.*,b.project_name from bs_contract a left join bs_project b on a.project_id = b.project_id where a.child_contract_inst_id = a.contract_inst_id and  a.sale_id = #{0} and a.state='1'"})
	@Results({ @Result(javaType=String.class, column = "project_id", property = "projectId"), @Result(javaType=String.class, column = "child_contract_inst_id", property = "childContractInstId"),
			@Result(javaType=String.class, column = "contract_inst_id", property = "contractInstId"),@Result(column = "contract_id", property = "contractId"),
			@Result(column = "contract_name", property = "contractName"),@Result(column = "file_path", property = "filePath"),
			@Result(column="sign_date",property="signDate"),@Result(column="exp_date",property="expDate"),
			@Result(column="eff_date",property="effDate"),@Result(javaType=String.class, column="sale_id",property="saleId"),
			@Result(column="state",property="state"),@Result(column="create_time",property="createTime"),
			@Result(javaType=String.class, column="create_user",property="createUser"),@Result(column="update_time",property="updateTime"),
			@Result(javaType=String.class, column="update_user",property="updateUser"),@Result(column="remark",property="remark"),
			@Result(column="project_name",property="projectName")
	})
	public List<Map<String, Object>> getBsContractsBySale(Long saleID);


	/*查询销售人员对应主合同信息
用途：我的合同
*/
	@Select(value = {"select a.*,b.project_name from bs_contract a left join bs_project b on a.project_id = b.project_id where a.sale_id = #{0} and a.state='1'"})
	@Results({ @Result(javaType=String.class, column = "project_id", property = "projectId"), @Result(javaType=String.class, column = "child_contract_inst_id", property = "childContractInstId"),
			@Result(javaType=String.class, column = "contract_inst_id", property = "contractInstId"),@Result(column = "contract_id", property = "contractId"),
			@Result(column = "contract_name", property = "contractName"),@Result(column = "file_path", property = "filePath"),
			@Result(column="sign_date",property="signDate"),@Result(column="exp_date",property="expDate"),
			@Result(column="eff_date",property="effDate"),@Result(javaType=String.class, column="sale_id",property="saleId"),
			@Result(column="state",property="state"),@Result(column="create_time",property="createTime"),
			@Result(javaType=String.class, column="create_user",property="createUser"),@Result(column="update_time",property="updateTime"),
			@Result(javaType=String.class, column="update_user",property="updateUser"),@Result(column="remark",property="remark"),
			@Result(column="project_name",property="projectName")
	})
	public List<Map<String, Object>> getBsContractsAllBySale(Long saleID);
	/*
	* 根据合同id（可以是父或子合同id）查询对应的主合同信息
	* */
	@Select(value = {"select * from bs_contract b where b.child_contract_inst_id = b.contract_inst_id and exists(select  a.contract_inst_id from bs_contract a " +
			" where  b.contract_inst_id = a.contract_inst_id and (a.child_contract_inst_id = #{0} or a.contract_inst_id = #{0}))"})
	@Results({ @Result(column = "project_id", property = "projectId"), @Result(column = "child_contract_inst_id", property = "childContractInstId"),
			@Result(column = "contract_inst_id", property = "contractInstId"),@Result(column = "contract_id", property = "contractId"),
			@Result(column = "contract_name", property = "contractName"),@Result(column = "file_path", property = "filePath"),
			@Result(column="sign_date",property="signDate"),@Result(column="exp_date",property="expDate"),
			@Result(column="eff_date",property="effDate"),@Result(column="sale_id",property="saleId"),
			@Result(column="state",property="state"),@Result(column="create_time",property="createTime"),
			@Result(column="create_user",property="createUser"),@Result(column="update_time",property="updateTime"),
			@Result(column="update_user",property="updateUser"),@Result(column="remark",property="remark")})
	public BsContract getBsContractsById(Long id);


	//查询项目主合同下的子合同信息
	@Select(value = {"select a.* from bs_contract a where a.child_contract_Inst_id != a.contract_inst_id "
			+ "and a.contract_inst_id = #{0} and a.state='1'"})
	@Results({ @Result(column = "project_id", property = "projectId"), @Result(column = "child_contract_inst_id", property = "childContractInstId"),
			@Result(column = "contract_inst_id", property = "contractInstId"),@Result(column = "contract_id", property = "contractId"),
			@Result(column = "contract_name", property = "contractName"),@Result(column = "file_path", property = "filePath"),
			@Result(column="sign_date",property="signDate"),@Result(column="exp_date",property="expDate"),
			@Result(column="eff_date",property="effDate"),@Result(column="sale_id",property="saleId"),
			@Result(column="state",property="state"),@Result(column="create_time",property="createTime"),
			@Result(column="create_user",property="createUser"),@Result(column="update_time",property="updateTime"),
			@Result(column="update_user",property="updateUser"),@Result(column="remark",property="remark")})
	public List<BsContract> getBsContractsChildById(Long contractInstId);
}



